# from util.Sql_df import SQLServer_df
# from util.sql_pyodbc import SQLServer_pyodbc
from api import sqlpyodbc,sqldf
from pydantic import BaseModel


# 获取所有欧盟返工数据
def GetEudata():
    sql = """
    SELECT C.CN_Name,D.ContractNO,D.LotNoDate,D.PCS,D.Qty FROM Country AS C RIGHT JOIN (
SELECT A.CountryCode,A.ContractNO,A.LotNoDate,A.PCS,B.Qty FROM Contract AS A RIGHT JOIN (
SELECT ContractNO,SUM(Qty) AS Qty FROM ConItem WHERE NeedRedCertificated=1 
GROUP BY ContractNO) AS B ON A.ContractNO=B.ContractNO) AS D ON C.CountryID=D.CountryCode
    """
    try:
        return \
            sqldf.Query(sql)
    except:
        return None


# 单条返工数据提交
def SubmitoneEudata(con, CN_Name, PCS, Qty):
    sqlList = []
    sqlList.append(f"UPDATE ConItem SET NeedRedCertificated=2 WHERE ContractNO='{con}' AND NeedRedCertificated=1")
    sqlList.append(f"INSERT INTO Eu_y(ContractNO,CN_Name,PCS,Qty) VALUES('{con}','{CN_Name}',{PCS},{Qty})")
    try:
        return sqlpyodbc.execute_transaction(sqlList)
    except:
        return None


# 批量提交返工数据
def SubmitEudata(data):
    sqlList = []
    print(data)
    for i in data:
        sqlList.append(
            f"UPDATE ConItem SET NeedRedCertificated=2 WHERE ContractNO='{i.ContractNO}' AND NeedRedCertificated=1")
        sqlList.append(
            f"INSERT INTO Eu_y(ContractNO,CN_Name,PCS,Qty) VALUES('{i.ContractNO}','{i.CN_Name}',{i.PCS},{i.Qty})")
    try:
        return sqlpyodbc.execute_transaction(sqlList)
    except:
        return False


# 获取已返工好的数据，主要用来做图表
def GetEudata_y():
    sql = """
    SELECT 
        CONVERT(date, NRC_Time) AS NRC_Time, 
        SUM(Qty) AS Qty
    FROM 
        Eu_y
    GROUP BY 
        CONVERT(date, NRC_Time)
    ORDER BY 
        NRC_Time DESC
    OFFSET 0 ROWS FETCH NEXT 15 ROWS ONLY;
    """

    try:
        return \
            sqldf.Query(sql)
    except:
        return None
